package Database;

import java.sql.*;
import java.text.SimpleDateFormat;
import Model.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.InputMismatchException;
import javax.swing.JOptionPane;

public class TableOperations
{
        private PreparedStatement stmt;
        private ResultSet rset;
        private Connection conn;
        private DBConnection db;
        private Date d = new Date();

        public TableOperations()
        {
        	db = new DBConnection();
            conn = db.openDB();
        }
        
        public void close()
        {
        	db.closeDB();
        }

        public boolean userLogin(String username, String password)
        {
                try
                {
                        Statement st = conn.createStatement();
                        String loginQuery = "SELECT password FROM Users WHERE username='"
                                        + username + "'";
                        System.out.println(loginQuery);
                        rset = st.executeQuery(loginQuery);
                        // checks if username and password are a match to a set in the
                        // database
                        for (int i = 0; i < rset.getFetchSize(); i++)
                        {
                                if (rset.next())
                                {
                                        String userPass = rset.getString(1);
                                        if (userPass.equals(password))
                                        {
                                                return true;
                                        }
                                }
                        }

                } catch (SQLException e)
                {
                        e.getMessage();
                }
                JOptionPane.showMessageDialog(null, "Username or password incorrect");
                return false;
        }

        public String getName(String username)
        {
                String name = null;
                try
                {
                        String sql = "select fname, lname from users where username = '"
                                        + username + "'";
                        stmt = conn.prepareStatement(sql);
                        rset = stmt.executeQuery();
                        // retrieves name of the current user
                        if (rset.next())
                        {
                                String f = rset.getString("fname");                            
                                String l = rset.getString("lname");
                                name =  f.trim() + " " + l.trim();
                        }                                
                } catch (SQLException e)
                {
                        JOptionPane.showMessageDialog(null, "Error- COuld not find name");
                }
                return name;
        }
       
        public SalesAssistant getSA(String username)
        {
                SalesAssistant sa = null;
                String sql = "select staffID,username,fname,lname,password from users where username = '" + username + "'";
                try
                {
                        stmt = conn.prepareStatement(sql);
                        rset = stmt.executeQuery();
                        if(rset.next())
                                sa = new SalesAssistant(rset.getInt(1), rset.getString(2), rset.getString(3), rset.getString(4), rset.getString(5));
                }
                catch(SQLException e)
                {
                        JOptionPane.showMessageDialog(null, "User not found");
                }
                return sa;
        }

        public boolean isManagerOrSA(String username)
        {
                boolean manager = false;
                try
                {
                        String query = "SELECT Manager.staffID, Users.username FROM Manager INNER JOIN Users ON Manager.staffID=Users.staffID AND username= '" + username + "'";
                        stmt = conn.prepareStatement(query);
                        rset = stmt.executeQuery();
                        // checks if user is a manager or sales assistant
                        for (int i = 0; i < rset.getFetchSize(); i++)
                        {
                                if (rset.next())
                                {
                                        String usernameDB = rset.getString(2);
                                        if (usernameDB.equals(username))
                                        {
                                                manager = true;
                                        }
                                }
                        }
                } catch (SQLException e)
                {
                        e.getMessage();
                }
                return manager;
        }
       
        public void becomeManager(User u)
        {
                try
                {
                        String query = "DELETE FROM SalesAssistant WHERE staffID= " +u.getStaffID();
                        stmt = conn.prepareStatement(query);
                        rset = stmt.executeQuery();
                       
                        conn.commit();
                       
                        query = "INSERT INTO Manager(staffID) values(?)";
                        stmt = conn.prepareStatement(query);
                        stmt.setInt(1, u.getStaffID());
                        stmt.executeQuery();
                       
                        conn.commit();
                                       
                        JOptionPane.showMessageDialog(null, "Manager Added");    
                } catch (SQLException e)
                {
                        e.getMessage();
                        JOptionPane.showMessageDialog(null, "Could not update user to Manager");
                }
        }
       
        public void becomeSA(User u)
        {
                try
                {
                        String query = "DELETE FROM Manager WHERE staffID= " +u.getStaffID();
                        stmt = conn.prepareStatement(query);
                        rset = stmt.executeQuery();
                       
                        query = "INSERT INTO SalesAssistant(staffID) values(?)";
                        stmt = conn.prepareStatement(query);
                        stmt.setInt(1, u.getStaffID());
                        stmt.executeQuery();
                       
                        conn.commit();
                                       
                        JOptionPane.showMessageDialog(null, "SalesAssistant Added");    
                } catch (SQLException e)
                {
                        e.getMessage();
                        JOptionPane.showMessageDialog(null, "Could not update user to SalesAssistant");
                }
        }

        public void queryProducts()
        {
                try
                {
                        String query = "SELECT productID, productName, price, inDate, quantity, cost, supplierID FROM Product";
                        stmt = conn.prepareStatement(query);
                        rset = stmt.executeQuery();
                        System.out.println("Product Table:");
                        while (rset.next())
                        {
                                System.out.println(rset.getInt(1) + " " + rset.getString(2) + " " + rset.getDouble(3) + " " + rset.getDate(4) + " "
                                                + rset.getInt(5) + " " + rset.getDouble(6) + " " + rset.getInt(7));
                        }
                } catch (SQLException e)
                {
                        e.getMessage();
                }
        }

        public Product populateProduct(int id)
        {
                Product p = null;
                try
                {
                        String sql = "select productname,cost,price,inDate,quantity,reorder_level,reorder_amount,supplierID from product where productID = " + id;
                        stmt = conn.prepareStatement(sql);
                        int quantity = 0;
                        int amount = 0;
                        int reorder = 0;
                        int sID = 0;
                        double cost = 0;
                        double price = 0;
                        String pName = null;
                        java.sql.Date date = null;
                        rset = stmt.executeQuery();
                        if(rset.next())
                        {
                                pName = rset.getString(1);
                                cost = rset.getDouble(2);
                                price = rset.getDouble(3);
                                date = rset.getDate(4);
                                quantity = rset.getInt(5);
                                reorder = rset.getInt(6);
                                amount = rset.getInt(7);
                                sID = rset.getInt(8);
                        }
                        p = new Product(id, pName, cost, price, date, quantity, reorder, amount, sID);
                }
                catch(SQLException e)
                {
                        e.printStackTrace();
                }
                return p;
        }
       
        public void insertSubs(int type)
        throws NumberFormatException
        {
                try
                {
                        String insert = null;
                        if(type == 1 || type == 2)
                {
                                int size = Integer.parseInt(JOptionPane.showInputDialog("What is the size of the bottle in millilitres?"));
                        insert = "insert into Drink(productID,bottlesize) values(prod_id.currval,?)";
                        stmt = conn.prepareStatement(insert);
                        stmt.setInt(1, size);
                        stmt.executeUpdate();
                       
                        if(type == 2)
                        {
                                int alc = Integer.parseInt(JOptionPane.showInputDialog("Enter alcohol percentage e.g. 35 (do not enter percent sign)"));
                                insert = "insert into Alcoholic(productID,alcoholcontent) values(prod_id.currval,?)";
                                stmt = conn.prepareStatement(insert);
                                stmt.setInt(1, alc);
                                stmt.executeUpdate();
                        }
                }
                        else if(type == 3)
                        {
                                int check = JOptionPane.showConfirmDialog(null, "Is this product toxic?", "Toxicity check", JOptionPane.YES_NO_CANCEL_OPTION, JOptionPane.QUESTION_MESSAGE);
                                if(check == JOptionPane.YES_OPTION)
                                        check = 1;
                                else
                                        check = 0;
                                insert = "insert into cleaningproduct(productID,toxic) values(prod_id.currval,?)";
                                stmt = conn.prepareStatement(insert);
                                stmt.setInt(1, check);
                                stmt.executeUpdate();
                        }
                        else if(type == 4)
                        {
                                int cal = Integer.parseInt(JOptionPane.showInputDialog("How many calories are in this product?"));
                                insert = "insert into food(productID,calories) values(prod_id.currval,?)";
                                stmt = conn.prepareStatement(insert);
                                stmt.setInt(1, cal);
                                stmt.executeUpdate();
                        }
                }
                catch(NumberFormatException nfe)
                {
                        JOptionPane.showMessageDialog(null, "Ensure numbers re in number format only with additional signs");
                        insertSubs(type);
                }
                catch(SQLException e)
                {
                        JOptionPane.showMessageDialog(null, "An error occured while trying to add product hierarchy");
                }
               
        }
       
        public boolean addProduct(Product p,int type)
        {
                boolean added = false;
                try
                {
                    // price validation
                    if (p.getPrice() > 20)
                    {
                        int confirm = JOptionPane.showConfirmDialog(null,"Are you sure you want to set the price as: " +
                                        p.getPrice(), "Price confirm", JOptionPane.YES_NO_CANCEL_OPTION, JOptionPane.QUESTION_MESSAGE);
                        if (confirm == JOptionPane.NO_OPTION)
                        {
                            double p1 = Double.parseDouble(JOptionPane.showInputDialog(null, "Enter price", "Price confirm",JOptionPane.QUESTION_MESSAGE));
                            p.setPrice(p1);
                        }
                    }

                    // product adding
                    String addQuery = "insert into Product(productID, productname,price,inDate,quantity,reorder_level,cost,SupplierID) values(prod_id.nextval,?,?,?,?,?,?,?)";
                    stmt = conn.prepareStatement(addQuery);
                    stmt.setString(1, p.getDescription());
                    stmt.setDouble(2, p.getPrice());
                    stmt.setDate(3, p.getSqlDate());
                    stmt.setInt(4, p.getQty());
                    stmt.setInt(5, p.getReorder());
                    stmt.setDouble(6, p.getCost());
                    stmt.setInt(7, p.getSupplierID());
                    stmt.executeUpdate();
                    insertSubs(type);
                    added = true;
                }                
                catch (SQLException e)
                {
                        JOptionPane.showMessageDialog(null, "Product not added");
                }
                return added;
        }

        public boolean updateProduct(Product p)
        {
                boolean updated = false;
                String update;
                try
                {
                        // if the price is greater than 20 verifies user wants to use that
                        // price
                        if (p.getPrice() > 20)
                        {
                                int confirm = JOptionPane.showConfirmDialog(null,"Are you sure you want to set the price as: " + p.getPrice(), "Price confirm",                                                
                                												JOptionPane.YES_NO_CANCEL_OPTION,JOptionPane.QUESTION_MESSAGE);
                                if (confirm == JOptionPane.NO_OPTION)
                                {
                                        double p1 = Double.parseDouble(JOptionPane.showInputDialog(null, "Enter price", "Price confirm",JOptionPane.QUESTION_MESSAGE));
                                        p.setPrice(p1);
                                        update = "update product set price = " + p.getPrice() + " where productid = " + p.getId();
                                        stmt = conn.prepareStatement(update);
                                        stmt.executeUpdate();
                                        System.out.println("price updated");
                                }
                        }

                        conn.setAutoCommit(false);
                        // productID number not null, productName varchar2(30), price float,
                        // inDate date, quantity number, cost float, supplierID
                        String queryString = "Select productID, productName, price, quantity, cost, supplierID from product where productID ="
                                        + p.getId();
                        stmt = conn.prepareStatement(queryString,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
                        rset = stmt.executeQuery();
                        if (rset.next()) // try to go to 1st row
                        {
                                rset.updateString("productName", p.getDescription());
                                rset.updateDouble("price", p.getPrice());
                                rset.updateInt("quantity", p.getQty());
                                rset.updateDouble("cost", p.getCost());
                                rset.updateInt("supplierID", p.getSupplierID());
                                updated = true;
                                rset.updateRow();
                        }
                        conn.commit();
                } catch (SQLException e)
                {
                        JOptionPane.showMessageDialog(null, "Product update Unsuccessful");
                }
                return updated;
        }

        public boolean deleteProduct(Product p)
        {
                try
                {
                        String update = "DELETE FROM Product WHERE productID = " + p.getId();
                        stmt = conn.prepareStatement(update);
                        stmt.executeUpdate();
                        return true;
                } catch (SQLException e)
                {
                        System.out.println(e);
                        return false;
                }
        }

        public boolean checkUsername(String username)
        {
                boolean available = true;
                try
                {
                        String query = "SELECT username FROM Users WHERE username= '"
                                        + username + "'";
                        stmt = conn.prepareStatement(query);
                        rset = stmt.executeQuery();
                        for (int i = 0; i < rset.getFetchSize(); i++)
                        {
                                if (rset.next())
                                {
                                        String usernameDB = rset.getString(1);
                                        if (usernameDB.equals(username))
                                        {
                                                available = false;
                                        }
                                }
                        }
                }
                catch (SQLException e)
                {
                        e.getMessage();
                }
                return available;
        }

        public void queryUsers()
        {
                try
                {
                        String query = "SELECT staffID, username, fname, lname, password FROM Users";
                        stmt = conn.prepareStatement(query);
                        rset = stmt.executeQuery();
                        System.out.println("User Table:");
                        while (rset.next())
                        {
                                System.out.println(rset.getInt(1) + " " + rset.getString(2)
                                                + " " + rset.getString(3) + " " + rset.getString(4)
                                                + " " + rset.getString(5));
                        }
                } catch (SQLException e)
                {
                        e.getMessage();
                }
        }

        public void addUser(User u, boolean manager)
        {
                try
                {
                        String username = u.getUsername();
                        String fname = u.getFname();
                        String lname = u.getLname();
                        String password = u.getPassword();

                        String insert = "insert into Users(staffID, username, fname, lname, password) values(staff_id.nextval,?,?,?,?)";
                        stmt = conn.prepareStatement(insert);
                        stmt.setString(1, username);
                        stmt.setString(2, fname);
                        stmt.setString(3, lname);
                        stmt.setString(4, password);
                        stmt.executeUpdate();

                        if (manager == true)
                        {
                                insert = "insert into Manager(staffID) values(staff_id.currval)";
                                stmt = conn.prepareStatement(insert);
                                stmt.executeUpdate();
                                JOptionPane.showMessageDialog(null, "Manager Added");
                        } else
                        {
                                insert = "insert into SalesAssistant(staffID) values(staff_id.currval)";
                                stmt = conn.prepareStatement(insert);
                                stmt.executeUpdate();
                                JOptionPane.showMessageDialog(null, "Sales Assistant Added");
                        }

                        conn.commit();
                }
                catch (Exception e)
                {
                        System.out.println("Could not add user " + e);
                }
        }

        public boolean updateUser(User u)
        {
                boolean updated = false;
                try
                {
                        conn.setAutoCommit(false);
                        String queryString = "Select username,fname,lname,password from users where staffID = "
                                        + u.getStaffID();
                        stmt = conn
                                        .prepareStatement(queryString,
                                                        ResultSet.TYPE_SCROLL_SENSITIVE,
                                                        ResultSet.CONCUR_UPDATABLE);
                        rset = stmt.executeQuery();
                        if (rset.next())
                        {
                                rset.updateString("username", u.getUsername());
                                rset.updateString("fname", u.getFname());
                                rset.updateString("lname", u.getLname());
                                rset.updateString("password", u.getPassword());
                               
                                rset.updateRow();
                        }
                        conn.commit();
                        updated = true;
                } catch (SQLException e)
                {
                        JOptionPane.showMessageDialog(null, "User update unsuccessful");
                }
                return updated;
        }

        public boolean deleteUser(User u)
        {
                try
                {
                        String delete = "DELETE FROM Users WHERE staffID = " + u.getStaffID();
                        stmt = conn.prepareStatement(delete);
                        stmt.executeUpdate();
                        return true;
                } catch (SQLException e)
                {
                        System.out.println("Delete unsuccessful");
                        return false;
                }
        }

        public void addSupplier(Supplier s)
        {
                try
                {
                        String insert = "insert into Supplier(SupplierID, name) values(supp_id.nextval,?)";
                        stmt = conn.prepareStatement(insert);
                        stmt.setString(1, s.getName());
                        stmt.executeUpdate();
                        JOptionPane.showMessageDialog(null, "Supplier Added");
                }
                catch (SQLException e)
                {
                        JOptionPane.showMessageDialog(null, "Supplier add failed");
                }
        }

        public boolean updateSupplier(Supplier s)
        {
                boolean updated = false;
                try
                {
                        conn.setAutoCommit(false);
                        String queryString = "SELECT name FROM Supplier WHERE supplierID = " + s.getSupplierID();
                        stmt = conn.prepareStatement(queryString, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                        rset = stmt.executeQuery();
                        if (rset.next())
                        {
                                rset.updateString("name", s.getName());
                                rset.updateRow();
                        }
                        conn.commit();
                        updated = true;
                } catch (SQLException e)
                {
                        JOptionPane.showMessageDialog(null, "Supplier update unsuccessful");
                }
                return updated;
        }

        public boolean deleteSupplier(Supplier s)
        {
                try
                {
                        String delete = "DELETE FROM Supplier WHERE supplierID = " + s.getSupplierID();
                        stmt = conn.prepareStatement(delete);
                        stmt.executeUpdate();
                        return true;
                } catch (SQLException e)
                {
                        System.out.println("Delete unsuccessful");
                        return false;
                }
        }

        public void addCustomer(LoyaltyCustomer c)
        {
                try
                {
                    String add = "insert into LoyaltyCustomer(cardID,c_name,address,phone,email,points,startDate) values (card_id.nextval,?,?,?,?,?,?)";
                    stmt = conn.prepareStatement(add);
                    stmt.setString(1, c.getName());
                    stmt.setString(2, c.getAddress());
                    stmt.setString(3, c.getPhone());
                    stmt.setString(4, c.getEmail());  
                    stmt.setInt(5, c.getPoints());
                    stmt.setDate(6, c.getInDate());
                    stmt.executeUpdate();

                } catch (SQLException e)
                {
                        JOptionPane.showMessageDialog(null, "Add customer failed");
                }
        }

        public boolean updateCustomer(LoyaltyCustomer c)
        {
                boolean updated = false;
                try
                {
                        conn.setAutoCommit(false);
                        String queryString = "SELECT c_name, address, phone, email FROM LoyaltyCustomer WHERE cardID = " + c.getCustID();
                        stmt = conn
                                        .prepareStatement(queryString,
                                                        ResultSet.TYPE_SCROLL_SENSITIVE,
                                                        ResultSet.CONCUR_UPDATABLE);
                        rset = stmt.executeQuery();
                        if (rset.next())
                        {
                                rset.updateString("c_name", c.getName());
                                rset.updateString("address", c.getAddress());
                                rset.updateString("phone", c.getPhone());
                                rset.updateString("email", c.getEmail());
                                rset.updateRow();
                        }
                        conn.commit();
                        updated = true;
                } catch (SQLException e)
                {
                        JOptionPane.showMessageDialog(null, "User update unsuccessful");
                }
                return updated;
        }

        public boolean deleteCustomer(LoyaltyCustomer c)
        {
                try
                {
                        String delete = "DELETE FROM LoyaltyCustomer WHERE cardID = " + c.getCustID();
                        stmt = conn.prepareStatement(delete);
                        stmt.executeUpdate();
                        return true;
                } catch (SQLException e)
                {
                        System.out.println("Delete unsuccessful");
                        return false;
                }
        }

        public ArrayList<User> searchUser(String name)
        {
                ArrayList<User> user = new ArrayList<User>();
                int userid;
                String fName = "";
                String lName = "";
                String username = "";
                String password = "";
                // searches for user with a name beginning with what is typed in
                String names = "SELECT staffID, username, fname, lname, password FROM USERS WHERE fname LIKE '" + name + "%'";
                try
                {
                        stmt = conn.prepareStatement(names);
                        rset = stmt.executeQuery();
                        for (int i = 0; i < rset.getFetchSize(); i++)
                        {
                                if (rset.next())
                                {
                                        userid = rset.getInt(1);
                                        username = rset.getString(2);
                                        fName = rset.getString(3);
                                        lName = rset.getString(4);
                                        password = rset.getString(5);
                                        user.add(new User(userid, username, fName, lName, password));
                                }
                        }
                } catch (SQLException e)
                {
                        e.getMessage();
                }
                return user;
        }

        public ArrayList<User> searchUser(int id)
        {
                ArrayList<User> user = new ArrayList<User>();
                int userid;
                String fName = "";
                String lName = "";
                String username = "";
                String password = "";
                try
                {
                        // searches for user with the same id as the one passed
                        String found = "SELECT staffID, username, fname, lname, password FROM USERS WHERE staffID= " + id;
                        stmt = conn.prepareStatement(found);
                        rset = stmt.executeQuery();
                        if (rset.next())
                        {
                                userid = rset.getInt(1);
                                username = rset.getString(2);
                                fName = rset.getString(3);
                                lName = rset.getString(4);
                                password = rset.getString(5);
                                user.add(new User(userid, username, fName, lName, password));
                        }
                } catch (InputMismatchException ime)
                {
                        id = Integer.parseInt(JOptionPane.showInputDialog(null,
                                        "Please input a number for id"));
                        searchUser(id);
                } catch (SQLException e)
                {
                        JOptionPane.showMessageDialog(null, "Search Unsuccessful");
                }
                return user;
        }

        public ArrayList<Product> searchProduct(String desc)
        {
                ArrayList<Product> product = new ArrayList<Product>();
                int productID, qty, supplierID,reorder,amount;
                String des = "";
                java.sql.Date date;
                double cost, price;
                // searches for product with a description beginning with what is typed in
                String names = "SELECT productID, productName, price, inDate, reorder_level, quantity, cost, reorder_amount, supplierID FROM Product WHERE productName LIKE '" + desc + "%'";
                try
                {
                        stmt = conn.prepareStatement(names);
                        rset = stmt.executeQuery();
                        for (int i = 0; i < rset.getFetchSize(); i++)
                        {
                                if (rset.next())
                                {
                                        productID = rset.getInt(1);
                                    des = rset.getString(2);
                                    price = rset.getDouble(3);
                                    date = rset.getDate(4);
                                    reorder = rset.getInt(5);
                                    qty = rset.getInt(6);
                                    cost = rset.getDouble(7);
                                    amount = rset.getInt(8);
                                    supplierID = rset.getInt(9);                                                                        
                                    product.add(new Product(productID,des,cost,price,date,qty,reorder,amount, supplierID));
                                }
                        }
                } catch (SQLException e)
                {
                        e.getMessage();
                }
                return product;
        }

        public ArrayList<Product> searchProduct(int id)
        {
                ArrayList<Product> product = new ArrayList<Product>();
                int productID, qty, supplierID,reorder,amount;
                String des = "";
                java.sql.Date date;
                double cost, price;
                // searches for product with a description beginning with what is typed in
                String names = "SELECT productID, productName, price, inDate, reorder_level, quantity, cost,reorder_amount, supplierID FROM Product WHERE productID = " + id;
                try
                {
                        stmt = conn.prepareStatement(names);
                        rset = stmt.executeQuery();
                        for (int i = 0; i < rset.getFetchSize(); i++)
                        {
                                if (rset.next())
                                {
                                        productID = rset.getInt(1);
                                    des = rset.getString(2);
                                    price = rset.getDouble(3);
                                    date = rset.getDate(4);
                                    reorder = rset.getInt(5);
                                    qty = rset.getInt(6);
                                    cost = rset.getDouble(7);
                                    amount = rset.getInt(8);
                                    supplierID = rset.getInt(9);                                                                        
                                    product.add(new Product(productID, des, cost, price, date, qty, reorder,amount, supplierID));
                                }
                        }
                }
                catch (NumberFormatException nfe)
                {
                        id = Integer.parseInt(JOptionPane.showInputDialog(null,"Please input a number for id"));
                        searchProduct(id);
                }
                catch (SQLException e)
                {
                        e.getMessage();
                }
                return product;
        }

        public ArrayList<LoyaltyCustomer> searchCustomer(String custName)
        {
                ArrayList<LoyaltyCustomer> customer = new ArrayList<LoyaltyCustomer>();
                int cardID = 0;
                String name = "";
                String address = "";
                String phone = "";
                String email = "";
                int points = 0;
                java.sql.Date d = null;
                // searches for product with a description beginning with what is typed
                String names = "SELECT cardID, c_name, address, phone, email,points,startDate FROM LoyaltyCustomer WHERE c_name LIKE '" + custName + "%'";
                try
                {
                        stmt = conn.prepareStatement(names);
                        rset = stmt.executeQuery();
                        for (int i = 0; i < rset.getFetchSize(); i++)
                        {
                                if (rset.next())
                                {
                                        cardID = rset.getInt(1);
                                        name = rset.getString(2);
                                        address = rset.getString(3);
                                        phone = rset.getString(4);
                                        email = rset.getString(5);
                                        points = rset.getInt(6);
                                        d = rset.getDate(7);
                                        customer.add(new LoyaltyCustomer(cardID, name, address, phone, email, d, points));
                                }
                        }
                } catch (SQLException e)
                {
                        e.getMessage();
                }
                return customer;
        }

        public ArrayList<LoyaltyCustomer> searchCustomer(int id)
        {
                ArrayList<LoyaltyCustomer> customer = new ArrayList<>();
                String name = "";
                String address = "";
                String phone = "";
                String email = "";
                int points = 0;
                java.sql.Date d = null;
                // searches for product with a description beginning with what is typed
                String names = "SELECT  c_name, address, phone, email,points,startDate FROM LoyaltyCustomer WHERE cardID= " + id;
                try
                {
                        stmt = conn.prepareStatement(names);
                        rset = stmt.executeQuery();
                        for (int i = 0; i < rset.getFetchSize(); i++)
                        {
                                if (rset.next())
                                {
                                        name = rset.getString(1);
                                        address = rset.getString(2);
                                        phone = rset.getString(3);
                                        email = rset.getString(4);
                                        points = rset.getInt(5);
                                        d = rset.getDate(6);
                                        customer.add(new LoyaltyCustomer(id, name, address, phone, email,d,points));
                                }
                        }
                } catch (SQLException e)
                {
                        JOptionPane.showMessageDialog(null, "Customer not found");
                }
                return customer;
        }

        public ArrayList<Supplier> searchSupplier(String supName)
        {
                ArrayList<Supplier> supplier = new ArrayList<Supplier>();
                int supplierID;
                String name = "";
                // searches for product with a description beginning with what is typed
                String names = "SELECT supplierID, name FROM Supplier WHERE name LIKE '" + supName + "%'";
                try
                {
                        stmt = conn.prepareStatement(names);
                        rset = stmt.executeQuery();
                        for (int i = 0; i < rset.getFetchSize(); i++)
                        {
                                if (rset.next())
                                {
                                        supplierID = rset.getInt(1);
                                        name = rset.getString(2);
                                        supplier.add(new Supplier(supplierID, name));
                                }
                        }
                } catch (SQLException e)
                {
                        e.getMessage();
                }
                return supplier;
        }

        public ArrayList<Supplier> searchSupplier(int id)
        {
                ArrayList<Supplier> supplier = new ArrayList<Supplier>();
                int supplierID;
                String name = "";
                // searches for product with a description beginning with what is typed
                String names = "SELECT supplierID, name FROM Supplier WHERE supplierID= " + id;
                try
                {
                        stmt = conn.prepareStatement(names);
                        rset = stmt.executeQuery();
                        for (int i = 0; i < rset.getFetchSize(); i++)
                        {
                                if (rset.next())
                                {
                                        supplierID = rset.getInt(1);
                                        name = rset.getString(2);
                                        supplier.add(new Supplier(supplierID, name));
                                }
                        }
                } catch (SQLException e)
                {
                        e.getMessage();
                }
                return supplier;
        }
       
        //checks if sufficient stock is available for sale
        public int checkStock(Product p,int reduce)
        {
        	 while (p.getQty() < reduce)
             {
                 reduce = Integer.parseInt(JOptionPane.showInputDialog(null, "Enter new quantity(Available stock: " + p.getQty() + ")", "Quantity change", JOptionPane.QUESTION_MESSAGE));
             }
        	 return reduce;
        }
        
        //stock to be sold
        public int stockOut(Product p, int reduce)
            throws NumberFormatException
        {
                // checks quantity in stock is greater than quantity demanded, if not
                // asks to change how much is being bought
               checkStock(p, reduce);
                try
                {
                        String sql = "update product set quantity = " + p.sellStock(reduce) + " where productID = " + p.getId();
                        stmt = conn.prepareStatement(sql);
                        stmt.executeUpdate();
                } 
                catch (NumberFormatException nfe)
                {
                        JOptionPane.showMessageDialog(null, "Please enter a quantity");
                } 
                catch (SQLException e)
                {
                        JOptionPane.showMessageDialog(null, "Stock has not been sold correctly");
                }
                return reduce;
        }

        // sale to nonLoyalty customer
        public double sellStock(Product[] p, int[] reduce, SalesAssistant sa)
                        throws InputMismatchException
        {
        	double totalCost = 0;
            try
            {
                
                // adds up total cost
                for (int i = 0; i < p.length; i++)
                {
                    stockOut(p[i], reduce[i]);
                    totalCost += (p[i].getPrice()*reduce[i]);
                }
       		 	       		 
                java.sql.Date date = new java.sql.Date(d.getTime());
                for (int i = 0; i < p.length; i++)
                {
                    // insert info into transactions
                    // card id 0 is treated as "public" id
                    String sql = "insert into transactions(transactionID,trans_date,total_cost,total_points,itemQuantity,cardID,staffID) values(trans_id.nextval,?,?,?,?,?,?)";
                    stmt = conn.prepareStatement(sql);                    
                    stmt.setDate(1, date);
                    stmt.setDouble(2, totalCost);
                    stmt.setInt(3, 0);
                    stmt.setInt(4, reduce[0]);
                    stmt.setInt(5, 0);
                    stmt.setInt(6, sa.getStaffID());
                    stmt.executeUpdate();
                    // insert into ProductTransaction
                    sql = "insert into producttransaction(transactionID,productID,quantity,price) values(trans_id.currval,?,?,?)";
                    stmt = conn.prepareStatement(sql);
                    stmt.setInt(1, p[i].getId());
                    stmt.setInt(2, reduce[i]);
                    stmt.setDouble(3, p[i].getPrice());
                    stmt.executeUpdate();                   
                }
                //checking if stock needs to be reordered
                for(int i = 0; i < p.length;i++)
                {
                	if(p[i].getReorder() > p[i].getQty())
                		stockIn(p[i], sa.getStaffID());
                }
            } 
            catch (InputMismatchException ime)
            {
                JOptionPane.showMessageDialog(null,"Please ensure that you have entered the correct information(e.g. price is a number)");
            } 
            catch (SQLException e)
            {
            	e.printStackTrace();
                JOptionPane.showMessageDialog(null, "Sale unsuccessful");
            }
            return totalCost;
        }

        // sale to loyalty customer
        public Double sellStock(Product[] p, int[] reduce, LoyaltyCustomer c, SalesAssistant sa)
        {
        	Double totalCost = 0.0;
	        try
	        {          
                Integer totalPoints = 0;

                for (int i = 0; i < p.length; i++)
                {
                    stockOut(p[i], reduce[i]);
                    totalCost += (p[i].getPrice()*reduce[i]);
                }
                totalPoints = totalCost.intValue();
                
                if (c.getPoints() > 25)
                {
                    int check = JOptionPane.showConfirmDialog(null, "Total cost is: " + totalCost + ", your point balance allows a discount of: " 
                    												+ (c.getPoints()/25) + "\nwould you lie to use your points?");
                    if (check == JOptionPane.YES_OPTION)
                    {
                        totalCost = c.usePoints(totalCost);
                    } 
                    else
                    {
                        c.addPoints(totalCost);
                    }
                }
                else
                	c.addPoints(totalPoints);
               
                java.sql.Date date = new java.sql.Date(d.getTime());
                // insert info into transactions and product transaction
                for (int i = 0; i < p.length; i++)
                {
                    String sql = "insert into transactions(transactionID,trans_date,total_cost,total_points,itemQuantity,cardID,staffID) values(trans_id.nextval,?,?,?,?,?,?)";
                    stmt = conn.prepareStatement(sql);
                    stmt.setDate(1, date);
                    stmt.setDouble(2, totalCost);                    
                    stmt.setInt(3, totalPoints);
                    stmt.setInt(4, reduce[0]);
                    stmt.setInt(5, c.getCustID());
                    stmt.setInt(6, sa.getStaffID());
                    stmt.executeUpdate();
                    // insert into ProductTransaction
                    sql = "insert into producttransaction(transactionID,productID,quantity,price) values(trans_id.currval,?,?,?)";
                    stmt = conn.prepareStatement(sql);
                    stmt.setInt(1, p[i].getId());
                    stmt.setInt(2, reduce[i]);
                    stmt.setDouble(3, p[i].getPrice());
                    stmt.executeUpdate();
                    // points added to customers account
                    System.out.println(c.getPoints());
                    sql = "update loyaltycustomer set points = " + c.getPoints() + " where cardID = " + c.getCustID();
                    stmt = conn.prepareStatement(sql);
                    stmt.executeUpdate();

                }
                //checking if stock needs to be reordered
                for(int i = 0; i < p.length;i++)
                {
                	if(p[i].getReorder() > p[i].getQty())
                		stockIn(p[i], sa.getStaffID());
                }
	        }
	        catch (SQLException e)
	        {
                JOptionPane.showMessageDialog(null, "Sale unsuccessful");
	        }
	        return totalCost;
        }
       //company taking in new stock
        public void stockIn(Product p, int staffID)
        {
                try
                {                              
                        String sql = "update product set quantity = quantity + " + p.getrAmount() + " where productID = " + p.getId();
                        stmt = conn.prepareStatement(sql);
                        stmt.executeUpdate();
               		
                        java.sql.Date date = new java.sql.Date(d.getTime());
                        sql = "insert into transactions(transactionID,trans_date,total_cost,total_points,itemQuantity,cardID,staffID) values(trans_id.nextval,?,?,?,?,?,?)";
                        stmt = conn.prepareStatement(sql);
                        stmt.setDate(1, date);
                        stmt.setDouble(2, (p.getrAmount()*p.getCost()*(-1)));                        
                        stmt.setInt(3, 0);
                        stmt.setInt(4, p.getrAmount());
                        stmt.setInt(5, -1);
                        stmt.setInt(6, staffID);
                        stmt.executeUpdate();
                        sql = "insert into producttransaction(transactionID,productID,quantity,price) values(trans_id.currval,?,?,?)";
                        stmt = conn.prepareStatement(sql);
                        stmt.setInt(1, p.getId());
                        stmt.setInt(2, p.getrAmount());
                        stmt.setDouble(3, (p.getCost()*(-1)));
                        stmt.executeUpdate();                          
                }
                catch(SQLException e)
                {
                        JOptionPane.showMessageDialog(null, "Error with reorder, please try again");
                        stockIn(p,staffID);
                }                              
        }
       
        //sets 1st loyalty card in the card view for sales assistant
        public ArrayList<LoyaltyCustomer> returnCList()
        {
                ArrayList<LoyaltyCustomer> lc = new ArrayList<>();
                try
                {
                        String sql = "select cardID,c_name,address,phone,email,points,startDate from loyaltycustomer where cardID > 0";
                        stmt = conn.prepareStatement(sql);
                        rset = stmt.executeQuery();
                        for(int i = 0; i < rset.getFetchSize();i++)
                        {
                                if(rset.next())
                                {
                                        LoyaltyCustomer c = new LoyaltyCustomer(rset.getInt(1), rset.getString(2), rset.getString(3), rset.getString(4), rset.getString(5), rset.getDate(7), rset.getInt(6));
                                        lc.add(c);
                                }
                        }
                }
                catch(SQLException e)
                {
                        System.out.println("Could not find customers");
                }
                return lc;
        }
        
        public ArrayList<Product> returnPList()
        {
                ArrayList<Product> p = new ArrayList<>();
                try
                {
                        String sql = "select productID,productname,price,inDate,reorder_level,quantity,cost,reorder_amount,supplierID from Product";
                        stmt = conn.prepareStatement(sql);
                        rset = stmt.executeQuery();
                        for(int i = 0; i < rset.getFetchSize();i++)
                        {
                                if(rset.next())
                                {
                                        Product pro = new Product(rset.getInt(1), rset.getString(2), rset.getDouble(7), rset.getDouble(3), rset.getDate(4), 
                                        							rset.getInt(6), rset.getInt(5), rset.getInt(8), rset.getInt(9));
                                        System.out.println(pro.getDescription());
                                        p.add(pro);
                                }
                        }
                }
                catch(SQLException e)
                {
                        System.out.println("Could not find Products");
                }
                return p;
        }
        
        public double[] CalculateProfit()
        {
        	java.sql.Date date = new java.sql.Date(d.getTime());
        	java.sql.Date sqlDate = new java.sql.Date(d.getTime());
        	sqlDate.setDate(sqlDate.getDate() - 7);
        	ArrayList<Double> sales = new ArrayList<>();
        	ArrayList<Double> purchases = new ArrayList<>();
        	ArrayList<Double> loyalty = new ArrayList<>();
        	String sql = "select transactionID from transactions where trans_date between ? and ?";
        	double[] results = new double[4];
        	try
        	{
        		stmt = conn.prepareStatement(sql);
        		stmt.setDate(1, sqlDate);
        		stmt.setDate(2, date);
        		rset = stmt.executeQuery();
        		ArrayList<Integer> id = new ArrayList<>();
        		//gets the transaction id's for all transactions in the past week
        		for(int i = 0; i < rset.getFetchSize(); i++)
        		{
        			if(rset.next())
        				id.add(rset.getInt(1));
        		}
        		
        		//adds the week's sales and purchases into arraylists        		
        		for(int i = 0; i < id.size(); i++)
        		{
        			sql = "select quantity,price from producttransaction where transactionid = " + id.get(i);
        			stmt = conn.prepareStatement(sql);
        			rset = stmt.executeQuery();
        			for(int j = 0; j < rset.getFetchSize(); j++)
            		{
            			if(rset.next())
            			{
            				if(rset.getInt(2) > 0)
            					sales.add(rset.getInt(1)*rset.getDouble(2));
            				else if(rset.getInt(2) < 0)
            					purchases.add(rset.getInt(1)*rset.getDouble(2));  
            				else
            					loyalty.add(rset.getDouble(2));
            			}
            		}
        		}
        		
        		double totalSales = 0;
        		double totalPurchases = 0;    		
        		double loyaltySales = 0;
        		//totals the array lists
        		for(int i = 0; i < sales.size(); i++)
        		{
        			totalSales += sales.get(i);
        		}
        		
        		for(int i = 0; i < purchases.size(); i++)
        		{
        			totalPurchases += purchases.get(i);
        		}        		
        		
        		for(int i = 0; i < loyalty.size(); i++)
        		{
        			loyaltySales += loyalty.get(i);
        		}
        		double profit = totalSales + totalPurchases;
        		results[0] = totalSales;
        		results[1] = totalPurchases;
        		results[2] = loyaltySales;
        		results[3] = profit;
        		
        	}
        	catch(SQLException e)
        	{
        		JOptionPane.showMessageDialog(null, "No transactions found");
        	}
        	return results;
        }
        
        public ArrayList<String> stockOrder()
        {
        	SimpleDateFormat f = new SimpleDateFormat("dd-MMM-yyyy");
        	ArrayList<String> transactions = new ArrayList<>();
        	try
        	{
        		String sql = "select p.productname, p.supplierID, pt.quantity, pt.price, t.cardid, s.name, t.trans_date from product p, producttransaction pt, transactions t, supplier s " +
        						"where p.productid = pt.productid and pt.transactionid = t.transactionid and p.supplierid = s.supplierid and cardid = -1 order by p.supplierid";
        		stmt = conn.prepareStatement(sql);
        		rset = stmt.executeQuery();
        		for(int i = 0; i < rset.getFetchSize(); i++)
        		{
        			if(rset.next())
        			{
        				String trans = rset.getInt(3) + " of product " + rset.getString(1) + " have been order at €" + (rset.getDouble(4) * (-1)) + " per unit from supplier : " + rset.getString(6) + 
        								" on " + f.format(rset.getDate(7)) + ".";
        				transactions.add(trans);
        			}
        		}
        		
        	}
        	catch(SQLException e)
        	{
        		e.printStackTrace();
        	}
        	return transactions;
        }
        
        public String[] getPopular()
        {
        	String[] pop = new String[5];
        	try
        	{
        		ArrayList<String> tempName = new ArrayList<>();
        		ArrayList<Integer> tempInt = new ArrayList<>();
        		String sql = "select p.productid, p.productname, pt.quantity from product p, producttransaction pt where p.productid = pt.productid " +
        					"order by pt.quantity desc";
        		stmt = conn.prepareStatement(sql);
        		rset = stmt.executeQuery();
        		for(int i = 0; i < rset.getFetchSize(); i++)
        		{
        			if(rset.next())
        			{    				
        				if(!tempName.contains(rset.getString(2)))
        				{
        					tempName.add(rset.getString(2));
        					tempInt.add(rset.getInt(3));
        				}					
        			}
        		}
        		String[] tempN = new String[tempName.size()];
        		int[] tempQty = new int[tempInt.size()];
        		for(int i = 0; i < tempN.length;i++)
        		{
        			tempN[i] = tempName.get(i);
        			tempQty[i] = tempInt.get(i);
        		}
        		
        		boolean sorted;
        		do
        		{
        			sorted = true;
        			for(int i = 0; i < (tempN.length - 1); i++)
        			{
        				if(tempQty[i] < tempQty[i+1])
        				{
        					sorted = false;
        					int temp = tempQty[i];
        					tempQty[i] = tempQty[i+1];
        					tempQty[i+1] = temp;
        					
        					String temp2 = tempN[i];
        					tempN[i] = tempN[i+1];
        					tempN[i+1] = temp2;
        				}
        			}
        		}
        		while(sorted == false);
        		
        		int num; 
        		if(tempN.length > 5)
        			num = 5;
        		else
        			num = tempN.length;
        		for(int i = 0; i < num; i++)
        		{
        			pop[i] = tempN[i];
        		}
        	}
        	catch(SQLException e)
        	{
        		JOptionPane.showMessageDialog(null, "No transactions found");
        	}
        	return pop;
        }
}